<?php
/*
  This file is part of Open-Doors.

  Open-Doors is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.
  
  Open-Doors is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.
  
  You should have received a copy of the GNU General Public License
  along with Open-Doors.  If not, see <http://www.gnu.org/licenses/>.

  Authors: Rune Thorbek & Araz B. Makoo
*/ 


/*
  Interface: Functions can be called by POST or GET as argument to
  function. E.g.
  db_functions.php?function=get_collectors?args=...

  For each of the following database tables: collectors, groups,
  venue, routes, and campaigns
  we will have the following functionality: commit, delete, and get.
  
  The example is given for the table collectors, but are also defined
  for the other tables (with small modification of arguments):

  commit_collector:
  - POST data: edit, (uid,) number, first_name, last_name, email_address,
               address, mobile_number, phone_number
    if edit="true" then update collector in collectors with uid 'uid'
    otherwise insert new collector in collectors (no uid set)

  delete_collector: 
  - POST data: uid
    deletes collector from collectors with uid 'uid'

  get_collectors:
  - GET data: start, limit, sort, filter
    Sencha specific interface function to lazy fetch data from collectors.
    It has an offset at 'start' and limits the fetch to 'limit'
    The arguments sort and filter are json encoded.

  In addition to that, there are a couple of additional functions as
  given below.

  return_group:
  - GET data: group_uid
    Return all the collectors in a given group.

  return_group_routes:
  - GET data: group_uid
    Returns all the routes for a given group.

  get_total_amount:
    Returns the total amount of money collected.

  export_csv_file:
    Exports data in CSV format.

*/


include 'db_connect.php';
include 'functions.php';


/* Check if user is logged in, otherwise kill the request. */
sec_session_start();
if(login_check($mysqli) != true) {
  die('{"success": false, "msg": "Authorization needed"}');
}

/* The following functions are helper functions, which cannot be called
   from the the web 'db_functions.php. */

function bit_to_ja_nej($bit) {
  if ($bit == 0)
    return "nej";
  else
    return "ja";
}

/* This function is used to remove escape strings from mysql queries
   to avoid sql-injections.  */
function my_escape($str) {
  // return mysql_real_escape_string($str);
  $str = str_replace("'", "\'",$str);
  $str = str_replace("\x00", "",$str);
  $str = str_replace("\n", "",$str);
  $str = str_replace("\r", "",$str);
  $str = str_replace("\x1a", "",$str);
  return $str;
}

/* Returns a POST value with name '$name', if not exists kill request. */
function getValue($name) {
  $value = $_POST[$name];
  if (!isset($value)) {
    $value = $_GET[$name];
    if (!isset($value)) {
      die("Missing parameter ".$name);
    }
  }
  return my_escape(utf8_decode($value));
}

function getValueDefault($name, $default) {
  $value = $_POST[$name];
  if (!isset($value)) {
    $value = $_GET[$name];
    if (!isset($value)) {
      return $default;
    }
  }
  if ($value == NULL) {
    return $default;
  }
  return my_escape(utf8_decode($value));
}

function getBooleanDefault($name, $default) {
  $value = $_POST[$name];
  if (!isset($value)) {
    $value = $_GET[$name];
    if (!isset($value)) {
      return $default;
    }
  }
  if ($value == NULL) {
    return $default;
  }
  if ($value == "true")
    return "true";
  return "false";
}

function my_stripslashes($data) {
  if (get_magic_quotes_gpc()) {
    return stripslashes($data);
  } else {
    return $data;
  }
}

/* Get a json encoded value with '$key' */
function get_json_value($json,$key) {
  $json_obj = json_decode(my_stripslashes($json), TRUE);
  if ($json_obj == FALSE) {
    die("ERROR: wrong JSON format");
  }
  $result = $json_obj[0][$key];
  return my_escape($result);
}

function get_filter_value($key) {
  $json_obj = json_decode(my_stripslashes($_GET['filter']), TRUE);
  //$json_obj = json_decode($_GET['filter'], TRUE);
 
  for($i = 0, $j = count($json_obj); $i < $j ; $i++) {
    if ($json_obj[$i]['property'] == $key) {
      return my_escape(utf8_decode($json_obj[$i]['value']));
    }
  }
  return null;
}

function modify_mysql_query_no_msg($mysqli, $sql, $failure_msg) {
    $result = $mysqli->query($sql);
    if (!$result) {
      echo '{"success": false, "msg": "'.$failure_msg.'"}';
      die("");
    }
}

function modify_mysql_query($mysqli, $sql, $success_msg, $failure_msg) {
    $result = $mysqli->query($sql);
    if (!$result) {
      echo '{"success": false, "msg": "'.$failure_msg.'"}';
      return;
    }
    echo '{"success": true, "msg": "'.$success_msg.'"}';
}

function check_query($mysqli, $sql, $failure_msg) {
  $result = $mysqli->query($sql);
  if (!$result) {
    echo '{"success": false, "msg": "'.$failure_msg.'"}';
    return;
  } 
  if ($result->num_rows == 0) {
    echo '{"success": false, "msg": "'.$failure_msg.'"}';
    return;
  }
}

function remove_empty_groups($mysqli) {
  $sql = "DELETE FROM groups WHERE uid IN (SELECT uid FROM (SELECT groups.uid FROM `".DATABASE."`.`groups` LEFT JOIN `".DATABASE."`.`collectors` ON groups.uid=collectors.group_uid WHERE collectors.group_uid IS NULL) as foo)";
  $result = $mysqli->query($sql);
}

function get_where_clause($csv) {
  //$uids = str_getcsv($csv);
  $uids = explode(',',$csv);
    $sql_end = "";
    $first = true;
    foreach($uids as $cuid) {
      $cuid = (int) trim($cuid);
      if ($first and strlen($cuid) > 0) {
	$sql_end = $sql_end." `uid`=".$cuid;
	$first = false;
      } else if (strlen($cuid) > 0) {
	$sql_end = $sql_end." OR `uid`=".$cuid;
      }
    }
    return $sql_end;
} 

function outputCSV($data) {
    $outstream = fopen("php://output", "w");
    function __outputCSV(&$vals, $key, $filehandler) {
        fputcsv($filehandler, $vals); // add parameters if you want
    }
    array_walk($data, "__outputCSV", $outstream);
    fclose($outstream);
}

function return_collectors($mysqli, $sql, $sql_size) {
  $size = $mysqli->query($sql_size);
  if (!$size) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
  $size = $size->fetch_row();

  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
	 
  $rows = array();
  while($row = $result->fetch_row()) {
    if ($row[1] ==  0 or $row[1] == null)
      $row[1] = "Nyoprettet";
    $row_enc = array(
		     "uid" => $row[0],
		     "number" => $row[1],
		     "first_name" => utf8_encode($row[2]),
		     "last_name" => utf8_encode($row[3]),
		     "email_address" =>  (utf8_encode($row[4])),
		     "address" =>  utf8_encode($row[5]),
		     "mobile_number" =>  utf8_encode($row[6]),
		     "phone_number" =>  utf8_encode($row[7]),
		     "group_uid" => $row[8],
		     "group_name" => utf8_encode($row[9]),
		     "campaign_uid" => $row[10],
		     "campaign_name" => utf8_encode($row[11]),
		     "venue_uid" => $row[12],
		     "venue_name" => utf8_encode($row[13]),
		     "no_collection_boxes" => $row[14]
		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}


function return_groups($mysqli, $sql, $sql_size) {
  $size = $mysqli->query($sql_size);
  if (!$size) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
  $size = $size->fetch_row();

  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }

  $rows = array();
  while($row = $result->fetch_row()) {
    $name = $row[1];
    $name = trim($name);
    if ($name == '' or $name == null) {
      $name = "[Tom gruppe]";
    }
    $total = $row[7] + $row[8] + $row[9];
    $row_enc = array(
		     "uid" => $row[0],
		     "name" => utf8_encode($name),
		     "venue_uids" => $row[2],
		     "no_collection_boxes" => $row[3],
		     "returned_collection_boxes" => $row[4],
		     "email_wish" =>  $row[5],
		     "same_route" => $row[6],
		     "amount_coins" => $row[7],
		     "amount_bank_notes" => $row[8],
		     "amount_app" => $row[9],
		     "total" => $row[10],//"".$total,
		     "comment" =>  utf8_encode($row[11]),
		     "no_members" =>  $row[12],
		     "no_routes" => "".$row[13],
		     "venue_name" => utf8_encode($row[14])
		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}

function return_groups_2($mysqli, $sql, $sql_size) {
  $size = $sql_size;
 
   $result = $mysqli->query($sql);
   if (!$result) {
     die("MySQL Error: ".$mysqli->error);	   
   }
 
   $rows = array();
   while($row = $result->fetch_row()) {
     $name = $row[1];
     $name = trim($name);
     if ($name == '' or $name == null) {
       $name = "[Tom gruppe]";
     }
     $total = $row[7] + $row[8] + $row[9];
     $row_enc = array(
 		     "uid" => $row[0],
 		     "name" => utf8_encode($name),
 		     "venue_uids" => $row[2],
 		     "no_collection_boxes" => $row[3],
 		     "returned_collection_boxes" => $row[4],
 		     "email_wish" =>  $row[5],
 		     "same_route" => $row[6],
 		     "amount_coins" => $row[7],
 		     "amount_bank_notes" => $row[8],
 		     "amount_app" => $row[9],
 		     "total" => $row[10],//"".$total,
 		     "comment" =>  utf8_encode($row[11]),
 		     "no_members" =>  $row[12],
 		     "no_routes" => "".$row[13],
 		     "venue_name" => utf8_encode($row[14])
 		     );
     $rows[]=$row_enc;
   }
   echo '({"total":"1","results":'.json_encode($rows).'})';
}

function return_groups_DUMMY($mysqli, $sql, $sql_size) {
 
   $rows = array();
     $row_enc = array(
 		     "uid" => 0,
 		     "name" => "",
 		     "venue_uids" => "",
 		     "no_collection_boxes" => "",
 		     "returned_collection_boxes" => "",
 		     "email_wish" =>  "",
 		     "same_route" => "",
 		     "amount_coins" => "",
 		     "amount_bank_notes" => "",
 		     "amount_app" => "",
 		     "total" => "",//"".$total,
 		     "comment" =>  "",
 		     "no_members" =>  "",
 		     "no_routes" => "",
 		     "venue_name" => ""
 		     );
     $rows[]=$row_enc;

   echo '({"total":"1","results":'.json_encode($rows).'})';
}

function return_routes($mysqli, $sql, $sql_size)  {
  $size = $mysqli->query($sql_size);
  if (!$size) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
  $size = $size->fetch_row();

  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }

  $rows = array();
  while($row = $result->fetch_row()) {
    $row_enc = array(
		     "uid" => $row[0],
		     "route_number" => $row[1],
		     "group_uid" =>  $row[2],
		     "group_name" => utf8_encode($row[3]),
		     "venue_uid" =>  $row[4],
		     "venue_name" => utf8_encode($row[5])
		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}

function return_campaigns($mysqli, $sql, $sql_size) {
  $size = $mysqli->query($sql_size);
  if (!$size) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
  $size = $size->fetch_row();
 
  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }
 
  $rows = array();
  while($row = $result->fetch_row()) {
    $row_enc = array(
 		     "uid" => $row[0],
 		     "name" => utf8_encode($row[1]),
 		     "no_collectors" =>  $row[2],
		     "total" => $row[3]
 		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}

function return_campaigns_2($mysqli, $sql, $sql_size) {
  $size = $sql_size;
 
  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }
 
  $rows = array();
  while($row = $result->fetch_row()) {
    $row_enc = array(
 		     "uid" => $row[0],
 		     "name" => utf8_encode($row[1]),
 		     "no_collectors" =>  "",
		     "total" => ""
 		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}

function return_venues($mysqli, $sql, $sql_size) {
  $size = $mysqli->query($sql_size);
  if (!$size) {
    die("MySQL ERROR: ".$mysqli->error);	   
  }
  $size = $size->fetch_row();
 
  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }
 
  $rows = array();
  while($row = $result->fetch_row()) {
    if ($row[7] == null)
      $row[7] = "0.00";
     $row_enc = array(
 		     "uid" => $row[0],
 		     "name" => utf8_encode($row[1]),
		     "address" => utf8_encode($row[2]),
		     "code" => utf8_encode($row[3]),
 		     "no_collectors" =>  $row[4],
		     "no_routes" => $row[5],
		     "taken_routes" => $row[6],
		     "total" => $row[7],
		     "no_groups" => $row[8],
		     "average_collected" => $row[10]
 		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"'.$size[0].'","results":'.json_encode($rows).'})';
}

function return_venues_2($mysqli, $sql, $sql_size) {
  $size = $sql_size;

  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }
 
  $rows = array();
  while($row = $result->fetch_row()) {
    if ($row[7] == null)
      $row[7] = "0.00";
     $row_enc = array(
 		     "uid" => $row[0],
 		     "name" => utf8_encode($row[1]),
		     "address" => utf8_encode($row[2]),
		     "code" => utf8_encode($row[3]),
 		     "no_collectors" =>  "",
		     "no_routes" => "",
		     "taken_routes" => "",
		     "total" => "",
		     "no_groups" => "",
		     "average_collected" => ""
 		     );
    $rows[]=$row_enc;
  }
  echo '({"total":"1","results":'.json_encode($rows).'})';
}

function generate_groups_for_excluded_collectors($mysqli) {
  $sql = "SELECT uid FROM `".DATABASE."`.`collectors` WHERE `group_uid`=0";
  $result = $mysqli->query($sql);
  if (!$result) {
    die("MySQL Error: ".$mysqli->error);	   
  }
  while($row = $result->fetch_row()) {
    $sql =  "INSERT INTO `".DATABASE."`.`groups` (`no_collection_boxes`, `returned_collection_boxes`, `email_wish`, `same_route`, `amount_coins`, `amount_bank_notes`, `amount_app`, `comment`, `collects_with_field`) VALUE (0,0,0,0,0,0,0,'','')";
    $mysqli->query($sql);
    $group_uid = mysqli_insert_id($mysqli);
    $sql = "UPDATE `".DATABASE."`.`collectors` SET `group_uid`=".$group_uid." WHERE uid=".$row[0];
    $mysqli->query($sql);
  }
}

function get_collectors_sql() {
  $sql_collectors = "SELECT uid,number,first_name,last_name,email_address,address,mobile_number,phone_number,group_uid as gid,(SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=gid) as group_name, campaign_uid as cid, (SELECT name FROM `".DATABASE."`.`campaigns` WHERE uid=cid), venue_uid as vid, (SELECT name FROM `".DATABASE."`.`venues` WHERE uid=vid) as venue_name, (SELECT no_collection_boxes FROM `".DATABASE."`.`groups` WHERE uid=group_uid) FROM `".DATABASE."`.`collectors`";
  return $sql_collectors;
}

function get_group_sql() {
    $sql_name = "(SELECT *, (SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as name";
    //$sql_name = "(SELECT *, 'group name' as name";

    $sql_venue_uids = "(SELECT GROUP_CONCAT('V', venue_uid, 'V') FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as venue_uids";
    //$sql_venue_uids = "'venueuids' as venue_uids";

    $sql_venue_names = "(SELECT GROUP_CONCAT(' ', name) FROM (SELECT DISTINCT name, group_uid FROM `".DATABASE."`.`collectors` INNER JOIN `".DATABASE."`.`venues` ON `collectors`.`venue_uid`=`venues`.`uid`) as bar WHERE `group_uid`=groups.uid) as venue_name";
    //$sql_venue_names = "'groupvenue' as venue_name";

    $sql = "SELECT uid as id,name,venue_uids,no_collection_boxes,returned_collection_boxes,email_wish,same_route,amount_coins,amount_bank_notes,amount_app,amount_coins + amount_bank_notes + amount_app as total,comment,(SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE group_uid=id) as no_members, (SELECT COUNT(uid) FROM `".DATABASE."`.`routes` WHERE group_uid=id) as no_routes,venue_name FROM ".$sql_name.", ".$sql_venue_uids.", ".$sql_venue_names." FROM `".DATABASE."`.`groups`) as foo";

    return $sql;
}


function get_group_sql2() {
    $sql_name = "(SELECT *, (SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as name";
    //$sql_name = "(SELECT *, 'group name' as name";

    $sql_venue_uids = "(SELECT GROUP_CONCAT('V', venue_uid, 'V') FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as venue_uids";
    //$sql_venue_uids = "'venueuids' as venue_uids";

    $sql_venue_names = "(SELECT GROUP_CONCAT(' ', name) FROM (SELECT DISTINCT name, group_uid FROM `".DATABASE."`.`collectors` INNER JOIN `".DATABASE."`.`venues` ON `collectors`.`venue_uid`=`venues`.`uid`) as bar WHERE `group_uid`=groups.uid) as venue_name";
    //$sql_venue_names = "'groupvenue' as venue_name";

    $sql = "SELECT uid as id,name,venue_uids,no_collection_boxes,returned_collection_boxes,email_wish,same_route,amount_coins,amount_bank_notes,amount_app,amount_coins + amount_bank_notes + amount_app as total,comment, 0 as no_members, 0 as no_routes,venue_name FROM ".$sql_name.", uid as venue_uids, ".$sql_venue_names." FROM `".DATABASE."`.`groups`) as foo";

    return $sql;
}

function get_route_sql() {
    $sql = "SELECT uid,route_number,group_uid as gid,(SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=gid) as group_name,venue_uid as vid, (SELECT name FROM `".DATABASE."`.`venues` WHERE uid=vid) as venue_name FROM `".DATABASE."`.`routes`";

    return $sql;
}

function get_campaign_sql() {
    $sql_amount = "(SELECT SUM(amount_coins) + SUM(amount_bank_notes) + SUM(amount_app) FROM (SELECT amount_coins, amount_bank_notes, amount_app, campaign_uid FROM `".DATABASE."`.`groups` INNER JOIN `".DATABASE."`.`collectors` ON `groups`.`uid`=`collectors`.`group_uid` GROUP BY `collectors`.`group_uid`) as foo WHERE campaign_uid=cid)";

    $sql = "SELECT uid as cid,name, (SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE campaign_uid=cid) as no_collectors, ".$sql_amount." as total FROM `".DATABASE."`.`campaigns`";

    return $sql;
}

function get_venue_sql() {
    $sql = "SELECT uid as vid,name,address,code, (SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE venue_uid=vid) as no_collectors, (SELECT COUNT(uid) FROM `".DATABASE."`.`routes` WHERE venue_uid=vid) as no_routes, (SELECT COUNT(uid) FROM `".DATABASE."`.`routes` WHERE venue_uid=vid AND group_uid IS NOT NULL AND group_uid!=0) as taken_routes, @total_amount :=(SELECT SUM(amount_coins) + SUM(amount_bank_notes) + SUM(amount_app) FROM (SELECT amount_coins, amount_bank_notes, amount_app, venue_uid FROM `".DATABASE."`.`groups` LEFT JOIN `".DATABASE."`.`routes` ON `groups`.`uid`=`routes`.`group_uid` GROUP BY `routes`.`group_uid`) as foo WHERE `venue_uid`=vid) as total, @groups:=(SELECT COUNT(DISTINCT group_uid) FROM `".DATABASE."`.`collectors` WHERE `venue_uid`=vid) as no_groups, @groups_returned:=(SELECT COUNT(DISTINCT group_uid) FROM `".DATABASE."`.`collectors` INNER JOIN `".DATABASE."`.`groups` ON `groups`.`uid`=`collectors`.`group_uid` WHERE `venue_uid`=vid AND `no_collection_boxes`!=0 AND `no_collection_boxes`=`returned_collection_boxes`) as no_groups_returned, ROUND(IFNULL(@total_amount/@groups_returned,0.00),2) as average_collected FROM `".DATABASE."`.`venues`";
    return $sql;
}

/* This class is only to have all the functions defined together.
   This ensures that they are the only functions that can be called
   from outside. */
class UserFunctions {

  protected $mysqli;

  function __construct($mysqli) {
    $this->mysqli = $mysqli;
  }

  function get_login_username() {
    echo '{"success": true, "msg": "'.$_SESSION['username'].'"}';
  }

  /* Commit (edit or insert) a collectors data into the collectors
     database table */
  function commit_collector() {
    $edit          = getValue("edit");
    $number        = (int) getValueDefault("number", 0);
    // TODO: Temporary fix
    if ($number == "Ny oprettet") {
      $number = 0;
    }
    $first_name    = getValue("first_name");
    $last_name     = getValue("last_name");
    $email_address = getValue("email_address");
    $address       = getValue("address");
    $mobile_number = getValue("mobile_number");
    $phone_number  = getValue("phone_number");
    $group_uid     = (int) getValueDefault("group_uid", 0);
    $campaign_uid  = (int) getValueDefault("campaign_uid", 0);
    $venue_uid     = (int) getValueDefault("venue_uid", 0);

    if ($first_name == null or $first_name == "") {
      echo '{"success": false, "msg": "Fejl: Fornavn ikke angivet."}';
      return;
    }
    if ($last_name == null or $last_name == "") {
      echo '{"success": false, "msg": "Fejl: Efternavn ikke angivet."}';
      return;
    }

    if ($group_uid == 0) {
      $sql = "INSERT INTO `".DATABASE."`.`groups` (`no_collection_boxes`, `returned_collection_boxes`, `email_wish`, `same_route`, `amount_coins`, `amount_bank_notes`, `amount_app`, `comment`) VALUE (0,0,0,0,0,0,0,'')";
      modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
      $group_uid = mysqli_insert_id($this->mysqli);
    }

    if ($edit == "true") {
      $uid         = (int) getValue("uid");
      $sql = "UPDATE `".DATABASE."`.`collectors` SET `number`=".$number.", `first_name`='".$first_name."', `last_name`='".$last_name."', `email_address`='".$email_address."', `address`='".$address."', `mobile_number`='".$mobile_number."', `phone_number`='".$phone_number."', `group_uid`=".$group_uid.", `campaign_uid`=".$campaign_uid.", `venue_uid`=".$venue_uid." WHERE `uid`=".$uid;
    } else {
      $sql = "INSERT INTO `".DATABASE."`.`collectors` (`number`,`first_name`, `last_name`, `email_address`, `address`, `mobile_number`, `phone_number`, `group_uid`, `campaign_uid`, `venue_uid`) VALUE (".$number.",'".$first_name."','".$last_name."','".$email_address."','".$address."','".$mobile_number."','".$phone_number."', ".$group_uid.", ".$campaign_uid.", ".$venue_uid.")";
    }
    $success_msg = "Data indsat i tabel.";
    $failure_msg = "Data ikke opdateret. Indsamler måske slettet. Prøv igen.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    if ($edit != "true") {
      $uid = mysqli_insert_id($this->mysqli);
    }
    $sql = "SELECT uid FROM `".DATABASE."`.`collectors` WHERE uid=".$uid;
    check_query($this->mysqli, $sql, $failure_msg);
    remove_empty_groups($this->mysqli);

    $sql = get_collectors_sql();
    $sql = $sql." WHERE uid=".$uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE uid=".$uid;
    return_collectors($this->mysqli, $sql, $sql_size);
  }


  function delete_collector() {
    $uid   = (int) getValue("uid");
    $sql   = "DELETE FROM `".DATABASE."`.`collectors` WHERE uid = '".$uid."'";
    $success_msg = "Rækken er slettet.";
    $failure_msg = "Rækken blev ikke slettet. Prøv igen.";
    modify_mysql_query($this->mysqli, $sql, $success_msg, $failure_msg);
    remove_empty_groups($this->mysqli);
  }

  function get_collectors() {
    $start     = getValue('start');
    $limit     = getValue('limit');
    $sort      = get_json_value($_GET['sort'],'property');
    $direction = get_json_value($_GET['sort'],'direction');

    $sql = get_collectors_sql();

    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`collectors`";

    $search_value = get_filter_value("search_value");
    
    if ($search_value == null) {
      $search_value = getValueDefault("search_value", null);
    }

    $venue_uid = get_filter_value("chosen_venue_value_uid");

    $where_clause_used = false;
    $where_clause = "";
    if ($search_value != null) {
      $where_clause_used = true;
      $where_clause = " WHERE (CONCAT(first_name, ' ', last_name) like '%".$search_value."%')";
      // $where_clause = $where_clause."email_address like '%".$search_value."%' or address like '%".$search_value."%' or ";
      // $where_clause = $where_clause."mobile_number like '%".$search_value."%' or phone_number like '%".$search_value."%' or number like '%".$search_value."%')";
    }
    if ($venue_uid != null) {
      if ($where_clause_used == true) {
	$where_clause = $where_clause." AND ";
      } else {
	$where_clause = $where_clause." WHERE ";
      }
      $where_clause = $where_clause."(venue_uid=".$venue_uid.")";
    }

    $sql      = $sql.$where_clause;
    $sql_size = $sql_size.$where_clause;

    $sql = $sql." ORDER BY ".$sort." ".$direction." LIMIT ".$start.",".$limit;
    return_collectors($this->mysqli, $sql, $sql_size);
  }

  function return_group() {
    $group_uid =  (int) get_filter_value('group_uid');
    $sql = get_collectors_sql();
    $sql = $sql." WHERE `group_uid`=".$group_uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=".$group_uid;
    return_collectors($this->mysqli, $sql, $sql_size);
  }

  function return_group_routes() {
    $group_uid = (int) get_filter_value('group_uid');
    $sql = get_route_sql();
    $sql = $sql." WHERE group_uid=".$group_uid;
    $sql_size = "SELECT COUNT(uid)  FROM `".DATABASE."`.`routes` WHERE group_uid=".$group_uid;
    return_routes($this->mysqli, $sql, $sql_size);
  }

  function get_groups() {
    $start     = (int) getValue('start');
    $limit     = (int) getValue('limit');
    $sort      = get_json_value($_GET['sort'],'property');
    $direction = get_json_value($_GET['sort'],'direction');

    $group_uid = get_filter_value("uid_value");

    // if ($group_uid != null) {
    //   return return_groups_DUMMY($this->mysqli, $sql, 1);
    // }

    if ($group_uid != null) {
      $sql = get_group_sql2();
      $sql = $sql." WHERE uid=".$group_uid;
      return return_groups_2($this->mysqli, $sql, 1);
    }
    
    $sql = get_group_sql();

    $sql_size = "SELECT COUNT(uid) FROM (SELECT *, (SELECT GROUP_CONCAT(' ', first_name, ' ', last_name) FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as name, (SELECT GROUP_CONCAT('V', venue_uid, 'V') FROM `".DATABASE."`.`collectors` WHERE `group_uid`=groups.uid) as venue_uids   FROM `".DATABASE."`.`groups`) as foo";

    $search_value = get_filter_value("search_value");
    
    if ($search_value == null) {
      $search_value = getValueDefault("search_value", null);
    }

    $venue_uid = get_filter_value("chosen_venue_value_uid");

    $where_clause_used = false;
    $where_clause = "";
    if ($search_value != null) {
      $where_clause_used = true;
      $where_clause = " WHERE (name like '%".$search_value."%')";
    }
    if ($venue_uid != null) {
      if ($where_clause_used == true) {
	$where_clause = $where_clause." AND ";
      } else {
	$where_clause = $where_clause." WHERE ";
      }
      $where_clause = $where_clause."(venue_uids like '%V".$venue_uid."V%')";
    }

    $sql      = $sql.$where_clause;
    $sql_size = $sql_size.$where_clause;

    $sql = $sql." ORDER BY ".$sort." ".$direction." LIMIT ".$start.",".$limit;
    return_groups($this->mysqli, $sql, $sql_size);
  }

  /* Commit (edit or insert) a group data into the groups
     database table */
  function commit_group() {
    $edit          = getValue("edit");

    //$name                      = getValue("name");
    $no_collection_boxes       = (int) getValueDefault("no_collection_boxes", 0);
    $returned_collection_boxes = (int) getValueDefault("returned_collection_boxes",0);
    $email_wish                = getBooleanDefault("email_wish", "false");
    $same_route                = getBooleanDefault("same_route", "false");
    $amount_coins              = (float) str_replace(",", ".", getValueDefault("amount_coins", 0));
    $amount_bank_notes         = (float) str_replace(",", ".", getValueDefault("amount_bank_notes", 0));
    $amount_app                = (float) str_replace(",", ".", getValueDefault("amount_app", 0));
    $comment                   = getValue("comment");

    $collectors                = getValue("indsamlere");
    $routes                    = getValue("ruter");

    if ($edit == "true") {
      $uid         = getValue("uid");
      $sql = "UPDATE `".DATABASE."`.`groups` SET `no_collection_boxes`=".$no_collection_boxes.", `returned_collection_boxes`=".$returned_collection_boxes.", `email_wish`=".$email_wish.", `same_route`=".$same_route.", `amount_coins`=".$amount_coins.", `amount_bank_notes`=".$amount_bank_notes.", `amount_app`=".$amount_app.", `comment`='".$comment."' WHERE `uid`=".$uid;
    } else {
      $sql = "INSERT INTO `".DATABASE."`.`groups` (`no_collection_boxes`, `returned_collection_boxes`, `email_wish`, `same_route`, `amount_coins`, `amount_bank_notes`, `amount_app`, `comment`) VALUE (".$no_collection_boxes.",".$returned_collection_boxes.",".$email_wish.",".$same_route.",".$amount_coins.",".$amount_bank_notes.",".$amount_app.",'".$comment."')";
    }
    $success_msg = "Data indsat i tabel.";
    $failure_msg = "Data blev ikke opdateret. Gruppe måske slettet. Prøv igen.";

    if ($edit == "true") {
      if ($routes != "") {
	$sql_end_routes = get_where_clause($routes);
	$sql_check_routes = "SELECT uid FROM `".DATABASE."`.`routes` WHERE (".$sql_end_routes.") AND group_uid!=".$uid;
	$result = $this->mysqli->query($sql_check_routes);
	if (!$result) {
	  echo '{"success": false, "msg": "Fejl: Fejl i ruter."}';
	  return;
	}
	if ($result->num_rows >= 1) {
	  echo '{"success": false, "msg": "Fejl: En af de angivne ruter er taget af en anden gruppe."}';
	  return;
	}
      }
    }

    if ($edit == "true") {
      $sql_delete = "UPDATE `".DATABASE."`.`collectors` SET `group_uid`=NULL WHERE group_uid=".$uid;
      modify_mysql_query_no_msg($this->mysqli, $sql_delete, $failure_msg);
      $sql_delete = "UPDATE `".DATABASE."`.`routes` SET `group_uid`=NULL WHERE group_uid=".$uid;
      modify_mysql_query_no_msg($this->mysqli, $sql_delete, $failure_msg);
    }

    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    if ($edit != "true") {
      $uid = mysqli_insert_id($this->mysqli);
    }
    if ($routes != "") {
      $sql_end_routes = get_where_clause($routes);
      $sql_insert = "UPDATE `".DATABASE."`.`routes` SET `group_uid`=".$uid." WHERE".$sql_end_routes;
      modify_mysql_query_no_msg($this->mysqli, $sql_insert, $failure_msg);
      
    }
    if ($collectors) {
      $sql_end_collectors = get_where_clause($collectors);
      $sql_insert = "UPDATE `".DATABASE."`.`collectors` SET `group_uid`=".$uid." WHERE".$sql_end_collectors;

      modify_mysql_query_no_msg($this->mysqli, $sql_insert, $failure_msg);
    } 

    $sql = "SELECT uid FROM `".DATABASE."`.`groups` WHERE uid=".$uid;
    check_query($this->mysqli, $sql, $failure_msg);
    remove_empty_groups($this->mysqli);

    $sql = get_group_sql();
    $sql = $sql." WHERE uid=".$uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`groups` WHERE uid=".$uid;
    generate_groups_for_excluded_collectors($this->mysqli);
    return_groups($this->mysqli, $sql, $sql_size);
  }

  function delete_group() {
    $uid   = (int) getValue("uid");
    $sql   = "DELETE FROM `".DATABASE."`.`groups` WHERE uid = '".$uid."'";
    $failure_msg = "Rækken blev ikke slettet. Prøv igen.";
    $success_msg = "Rækken er slettet.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    $sql   = "UPDATE `".DATABASE."`.`routes` SET `group_uid`=NULL WHERE `group_uid`=".$uid;
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    $sql   = "UPDATE `".DATABASE."`.`collectors` SET `group_uid`=NULL WHERE `group_uid`=".$uid;
    modify_mysql_query($this->mysqli, $sql, $success_msg, $failure_msg);
 }


  function get_routes() {
    $start     = (int) getValue('start');
    $limit     = (int) getValue('limit');
    $sort      = get_json_value($_GET['sort'],'property');
    $direction = get_json_value($_GET['sort'],'direction');

    $sql = get_route_sql();

    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`routes`";

    $search_value = get_filter_value("search_value");

    if ($search_value == null) {
      $search_value = getValueDefault("search_value", null);
    }

    if ($search_value != null) {
      $sql = $sql." WHERE (route_number like '%".$search_value."%')";
      $sql_size = $sql_size." WHERE (route_number like '%".$search_value."%')";
    }

    $sql = $sql." ORDER BY ".$sort." ".$direction." LIMIT ".$start.",".$limit;
    return_routes($this->mysqli, $sql, $sql_size);
  }

  function commit_route() {
    $edit          = getValue("edit");

    $route_number      = (int) getValue("route_number");
    $group_uid         = (int) getValueDefault("group_uid", 0);
    $venue_uid         = (int) getValueDefault("venue_uid", 0);

    if ($group_uid == 0 or $group_uid == "0")
      $group_uid = "NULL";

    if ($edit == "true") {
      $uid               = (int) getValue("uid");
      $sql = "UPDATE `".DATABASE."`.`routes` SET `route_number`=".$route_number.", `group_uid`=".$group_uid.", `venue_uid`=".$venue_uid." WHERE `uid`=".$uid;
    } else {
      $sql = "INSERT INTO `".DATABASE."`.`routes` (`route_number`, `group_uid`, `venue_uid`) VALUE (".$route_number.",".$group_uid.",".$venue_uid.")";
    }
    $success_msg = "Data indsat i tabel.";
    $failure_msg = "Data blev ikke opdateret. Rute måske slettet. Prøv igen.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    if ($edit != "true") {
      $uid = mysqli_insert_id($this->mysqli);
    }
    
    $sql = "SELECT uid FROM `".DATABASE."`.`routes` WHERE uid=".$uid;
    check_query($this->mysqli, $sql, $failure_msg);

    $sql = get_route_sql();
    $sql = $sql." WHERE uid=".$uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`routes` WHERE uid=".$uid;
    return_routes($this->mysqli, $sql, $sql_size);

  }

  function delete_route() {
    $uid   = (int) getValue("uid");
    $sql   = "DELETE FROM `".DATABASE."`.`routes` WHERE uid = '".$uid."'";
    $failure_msg = "Rækken blev ikke slettet. Prøv igen.";
    $success_msg = "Rækken er slettet.";
    modify_mysql_query($this->mysqli, $sql, $failure_msg);
  }

  function get_campaigns() {
    $start     = (int) getValue('start');
    $limit     = (int) getValue('limit');
    $sort      = get_json_value($_GET['sort'],'property');
    $direction = get_json_value($_GET['sort'],'direction');
 
    $campaign_uid = get_filter_value("uid_value");

    if ($campaign_uid != null) {
      $sql = "SELECT uid,name FROM `".DATABASE."`.`campaigns` WHERE uid=".$campaign_uid;
      return return_campaigns_2($this->mysqli, $sql, 1);
    }

    $sql = get_campaign_sql();
 
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`campaigns`";
 
    $search_value = get_filter_value("search_value");
 
    if ($search_value == null) {
      $search_value = getValueDefault("search_value", null);
    }
 
    if ($search_value != null) {
      $sql = $sql." WHERE (name like '%".$search_value."%')";
      $sql_size = $sql_size." WHERE (name like '%".$search_value."%')";
    }
 
    $sql = $sql." ORDER BY ".$sort." ".$direction." LIMIT ".$start.",".$limit;
    return_campaigns($this->mysqli, $sql, $sql_size);
  }

  function commit_campaign() {
    $edit          = getValue("edit");
    $name          = getValue("name");
    if ($edit == "true") {
      $uid               = (int) getValue("uid");
      $sql = "UPDATE `".DATABASE."`.`campaigns` SET `name`='".$name."' WHERE `uid`=".$uid;
    } else {
      $sql = "INSERT INTO `".DATABASE."`.`campaigns` (`name`) VALUE ('".$name."')";
    }
    $success_msg = "Data indsat i tabel.";
    $failure_msg = "Data blev ikke Opdateret. Kampagne måske slettet. Prøv igen.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    if ($edit != "true") {
      $uid = mysqli_insert_id($this->mysqli);
    }
    $sql = "SELECT uid FROM `".DATABASE."`.`campaigns` WHERE uid=".$uid;
    check_query($this->mysqli, $sql, $failure_msg);

    $sql = get_campaign_sql();
    $sql = $sql." WHERE uid=".$uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`campaigns` WHERE uid=".$uid;
    return_campaigns($this->mysqli, $sql, $sql_size);
  }

  function delete_campaign() {
    $uid   = (int) getValue("uid");
    $sql   = "DELETE FROM `".DATABASE."`.`campaigns` WHERE uid = '".$uid."'";
    $failure_msg = "Rækken blev ikke slettet. Prøv igen.";
    $success_msg = "Rækken er slettet.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    $sql   = "UPDATE `".DATABASE."`.`collectors` SET `campaign_uid`=NULL WHERE `campaign_uid`=".$uid;
    modify_mysql_query($this->mysqli, $sql, $success_msg, $failure_msg);
  }

  function get_venues() {
    $start     = (int) getValue('start');
    $limit     = (int) getValue('limit');
    $sort      = get_json_value($_GET['sort'],'property');
    $direction = get_json_value($_GET['sort'],'direction');
 
    $venue_uid = get_filter_value("uid_value");
 
    if ($venue_uid != null) {
      $sql = "SELECT uid,name,address,code FROM `".DATABASE."`.`venues` WHERE uid=".$venue_uid;
      return return_venues_2($this->mysqli, $sql, 1);
    }

    $sql = get_venue_sql();

    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`venues`";
 
    $search_value = get_filter_value("search_value");
 
    if ($search_value == null) {
      $search_value = getValueDefault("search_value", null);
    }
 
    $where_clause = "";
    if ($search_value != null) {
      $where_clause = " WHERE (name like '%".$search_value."%')";
      $where_clause = $where_clause." or (address like '%".$search_value."%')";
      $where_clause = $where_clause." or (code like '%".$search_value."%')";
    }
    $sql = $sql.$where_clause;
    $sql_size = $sql_size.$where_clause;
 
    $sql = $sql." ORDER BY ".$sort." ".$direction." LIMIT ".$start.",".$limit;
    return_venues($this->mysqli, $sql, $sql_size);
  }

  function commit_venue() {
    $edit          = getValue("edit");

    $name          = getValue("name");
    $address       = getValue("address");
    $code          = getValueDefault("code", "");
    if ($edit == "true") {
      $uid               = (int) getValue("uid");
      $sql = "UPDATE `".DATABASE."`.`venues` SET `name`='".$name."', `address`='".$address."', `code`='".$code."' WHERE `uid`=".$uid;
    } else {
      $sql = "INSERT INTO `".DATABASE."`.`venues` (`name`, `address`, `code`) VALUE ('".$name."', '".$address."', '".$code."')";
    }
    $success_msg = "Data indsat i tabel.";
    $failure_msg = "Data blev ikke opdateret. Mødested måske slettet. Prøv igen.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    if ($edit != "true") {
      $uid = mysqli_insert_id($this->mysqli);
    }
    $sql = "SELECT uid FROM `".DATABASE."`.`venues` WHERE uid=".$uid;
    check_query($this->mysqli, $sql, $failure_msg);

    $sql = get_venue_sql();
    $sql = $sql." WHERE uid=".$uid;
    $sql_size = "SELECT COUNT(uid) FROM `".DATABASE."`.`venues` WHERE uid=".$uid;
    return_venues($this->mysqli, $sql, $sql_size);
  }

  function delete_venue() {
    $uid   = (int) getValue("uid");
    $sql   = "DELETE FROM `".DATABASE."`.`venues` WHERE uid = '".$uid."'";
    $failure_msg = "Rækken blev ikke slettet. Prøv igen.";
    $success_msg = "Rækken er slettet.";
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    $sql   = "UPDATE `".DATABASE."`.`routes` SET `venue_uid`=NULL WHERE `venue_uid`=".$uid;
    modify_mysql_query_no_msg($this->mysqli, $sql, $failure_msg);
    $sql   = "UPDATE `".DATABASE."`.`collectors` SET `venue_uid`=NULL WHERE `venue_uid`=".$uid;
    modify_mysql_query($this->mysqli, $sql, $success_msg, $failure_msg);
  }

  function get_total_amount() {
    $sql = "SELECT SUM(amount_coins) + SUM(amount_bank_notes) + SUM(amount_app) FROM groups;";
    $result = $this->mysqli->query($sql);
    if (!$result) {
      echo '{"success": false, "msg": "Fejl"}';
    } else {
      $result = $result->fetch_row();
      echo '{"success": true, "msg": "'.$result[0].'"}';
    }
  }

  function export_csv_file() {
    header("Content-type: text/csv");
    header("Content-Disposition: attachment; filename=file.csv");
    header("Pragma: no-cache");
    header("Expires: 0");

    $sql = "SELECT number, first_name, last_name, email_address, mobile_number, phone_number, name, email_wish, same_route, amount_coins + amount_bank_notes + amount_app FROM `".DATABASE."`.`collectors` INNER JOIN `".DATABASE."`.`groups` ON collectors.group_uid=groups.uid LEFT JOIN `".DATABASE."`.`campaigns` ON `campaigns`.`uid`=`collectors`.`campaign_uid`";

    $result = $this->mysqli->query($sql);
    if (!$result) {
      $array = array(array("Fejl"));
      outputCSV($array);
      return;
    }
    $data = array();
    $row_enc = array("indsamlernummer",
		     "navn",
		     "email",
		     "mobil",
		     "tlf",
		     "kampagne",
		     "email ønskes",
		     "samme rute næste år",
		     "beløb"
		     );
    $data[] =$row_enc;
    while($row = $result->fetch_row()) {
      $row_enc = array($row[0], 
		       utf8_encode($row[1]." ".$row[2]), 
		       $row[3],
		       $row[4],
		       $row[5],
		       $row[6],
		       bit_to_ja_nej($row[7]),
		       bit_to_ja_nej($row[8]),
		       $row[9]);
      $data[]=$row_enc;
    }
    outputCSV($data);
  }

}

/* This gets the function to call from the uri 
   example: db_functions.php?function=get_collectors
   Then $_GET['function'] will return 'get_collectors'
   Functions can also be called by post data in the same
   manner.
*/
$function_holder = (isset($_GET['function'])) ? $_GET['function'] : $_POST['function'];

/* Create an instance of class UserFunctions. */
$user_functions = new UserFunctions($mysqli);

/* Check if function $function_holder exists in class UserFunctions
   and call it if it does. */
if (method_exists($user_functions, $function_holder)) {
  $user_functions->$function_holder();
} else {
  echo "ERROR: Function '".$function_holder."' does not exist!";
}

?>